package com.qububu.db.dao.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.qububu.ad.dto.TweetReply;
import com.qububu.ad.dto.TweetInfo;
import com.qububu.ad.dto.TweetInfo.TweetStatus;
import com.qububu.db.dao.AdTweetDao;
import com.qububu.utils.ParamConverter;
import com.qububu.utils.SystemUtil;

@Repository
public class AdTweetDaoImpl implements AdTweetDao {

	private JdbcTemplate jdbcTemplate;
	
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public void doAddTweet(TweetInfo tweet) {
		String sql="insert into tweet(tweet_title, tweet_content, create_dttm, create_ip, tweet_status, author_id) values(?, ?, ?, ?, ?, ?)";
		
		Object[] data = {
				tweet.getTitle(),
				tweet.getContent(),
				tweet.getCreateDttm(),
				tweet.getCreateIp(),
				tweet.getTweetStatus().toIntValue(),
				ParamConverter.convertToLongObj(SystemUtil.decrypt(tweet.getAuthorId()))
		};
		
		jdbcTemplate.update(sql, data);
	}

	public void doDeleteTweet(String tweetId) {
//		String sql = "DELETE tr,t FROM tweet_reply tr,tweet t WHERE tr.tweet_id=t.tweet_id AND t.tweet_id=?";// PS:tweet_reply中含有tweet的外键,所以必须保证tweet_reply表放在from的前面
//		String sql = "DELETE tr,t FROM tweet_reply tr,tweet t WHERE tr.tweet_id=? AND tr.tweet_id=t.tweet_id";
		String replySql = "DELETE FROM tweet_reply WHERE tweet_id=?";
		String tweetSql = "DELETE FROM tweet WHERE tweet_id=?";
		long reTweetId = ParamConverter.convertToLongObj(SystemUtil.decrypt(tweetId));
//		jdbcTemplate.update(sql, new Object[]{reTweetId, reTweetId});
		jdbcTemplate.update(replySql, reTweetId);
		jdbcTemplate.update(tweetSql, reTweetId);
//		jdbcTemplate.update(sql);
	}

	public TweetInfo getTodayTweet() {
		TweetInfo tweet = null;
		String sql = "SELECT * FROM tweet WHERE tweet_status=? ORDER BY create_dttm DESC";
		List<Map<String, Object>> dataList = jdbcTemplate.queryForList(sql, TweetStatus.NORMAL.toIntValue());
		if (dataList != null && dataList.size() > 0) {
			Map<String, Object> elem = dataList.get(0);
			tweet = new TweetInfo(ParamConverter.convertToLongObj(String.valueOf(elem.get("tweet_id"))), 
					ParamConverter.convertToLongObj(String.valueOf(elem.get("author_id"))));
			tweet.setContent(String.valueOf(elem.get("tweet_content")));
			tweet.setTitle(String.valueOf(elem.get("tweet_title")));
			tweet.setCreateDttm((Integer) elem.get("create_dttm"));
		}
		
		return tweet;
	}

	public List<TweetInfo> getTweets() {
		String sql = "SELECT * FROM tweet ORDER BY create_dttm DESC";
		List<Map<String, Object>> dataList = jdbcTemplate.queryForList(sql);
		List<TweetInfo> resultList = new ArrayList<TweetInfo>();
		TweetInfo tweet = null;
		for (Map<String, Object> elem : dataList) {
			tweet = new TweetInfo(ParamConverter.convertToLongObj(String.valueOf(elem.get("tweet_id"))), 
					ParamConverter.convertToLongObj(String.valueOf(elem.get("author_id"))));
			tweet.setContent(String.valueOf(elem.get("tweet_content")));
			tweet.setTitle(String.valueOf(elem.get("tweet_title")));
			tweet.setCreateIp(String.valueOf(elem.get("create_ip")));
			tweet.setCreateDttm((Integer) elem.get("create_dttm"));
			tweet.setTweetStatus(TweetStatus.getTweetStatus((Integer) elem.get("tweet_status")));
			resultList.add(tweet);
		}
		return resultList;
	}

	public List<TweetReply> getTweetReplysByTweetId(String tweetId) {
		List<TweetReply> resultList = new ArrayList<TweetReply>();
		String sql = "SELECT * FROM tweet_reply WHERE tweet_id=?";
		List<Map<String, Object>> dataMap = jdbcTemplate.queryForList(sql, ParamConverter.convertToLongObj(SystemUtil.decrypt(tweetId)));
		TweetReply reply = null;
		for (Map<String, Object> elem : dataMap) {
			reply = new TweetReply(ParamConverter.convertToLongObj(String.valueOf(elem.get("reply_id"))));
			reply.setAuthorName(String.valueOf(elem.get("author_name")));
			reply.setContent(String.valueOf(elem.get("content")));
			reply.setCreateDttm((Integer) elem.get("create_dttm"));
			resultList.add(reply);
		}
		return resultList;
	}

	public void addTweetReply(TweetReply reply, String tweetId) {
		String sql="INSERT INTO tweet_reply(content, create_dttm, create_ip, author_name, tweet_id) values(?, ?, ?, ?, ?)";
		Object[] insertData = {
				reply.getContent(),
				reply.getCreateDttm(),
				reply.getCreateIp(),
				reply.getAuthorName(),
				ParamConverter.convertToLongObj(SystemUtil.decrypt(tweetId))
		};
		jdbcTemplate.update(sql, insertData);
	}

	public void changeTweetStatus(String tweetId, int status) {
		String sql= "UPDATE tweet SET tweet_status=? WHERE tweet_id=?";
		Object[] data = {
				status,
				ParamConverter.convertToInteger(SystemUtil.decrypt(tweetId))
		};
		jdbcTemplate.update(sql, data);
	}

	public TweetInfo getTweet(String tweetId) {
		String sql = "SELECT * FROM tweet WHERE tweet_id=?";
		TweetInfo tweet = null;
		List<Map<String, Object>> dataList = jdbcTemplate.queryForList(sql, ParamConverter.convertToInteger(SystemUtil.decrypt(tweetId)));
		if (dataList != null && dataList.size() > 0) {
			Map<String, Object> elem = dataList.get(0);
			tweet = new TweetInfo(ParamConverter.convertToLongObj(String.valueOf(elem.get("tweet_id"))), 
					ParamConverter.convertToLongObj(String.valueOf(elem.get("author_id"))));
			tweet.setContent(String.valueOf(elem.get("tweet_content")));
			tweet.setTitle(String.valueOf(elem.get("tweet_title")));
			tweet.setCreateDttm((Integer) elem.get("create_dttm"));
		}
		return tweet;
	}

}
